<?php
        include 'PHPClasses/RelationshipHandler.php';

class SubjectDBAccess extends DBAccess {
    //put your code here
    
    private $idSubjectGenerated;
    private $relationshipHandler;
   
    public function _construct(){
        parent::__construct();
       
       
    }   
    
     public function getIdSubjectGenerated() {
        return $this->idSubjectGenerated;
    }

    public function setIdSubjectGenerated($idSubjectGenerated) {
        $this->idSubjectGenerated = $idSubjectGenerated;
    }
    
    public function validate($Subject,$gradeCriteria1st,$gradeCriteria2nd,$gradeCriteria3rd,$gradeCriteria4th,$registered,$OverOrUnder100)
    {
        $errormsg = "";
        $name=$Subject->getSubjectName();
        $level=$Subject->getSubjectLevel();
        $grade=$Subject->getPassingGrade();
       
        //Check empty fields
        if($name&&$grade&&!empty($level)&&!empty($gradeCriteria1st)&&!empty($gradeCriteria2nd)&&!empty($gradeCriteria3rd)&&!empty($gradeCriteria4th)){}
        else  
            $errormsg = "Please fill up all text fields";
                                            
        $subjectList=mysql_query("SELECT * FROM subject WHERE subjectName='$name'") or 
             die('Could not connect3: ' . mysql_error());
                                            
       //Check if subject is already in database
        if(mysql_num_rows($subjectList) > 0&&!$registered)
        {
            if ($errormsg) //If there is already an error, add next error  
                $errormsg = $errormsg . " & subject is already registered";  
            else  
                $errormsg = "Subject is already registered"; 
        }
        
        
       //Check if total grade criteria percentage is equal to 100
        if($OverOrUnder100){
                if($errormsg)
                       $errormsg = $errormsg. " &  the total grade criteria percentage for each quarter should equal to 100%";

                   else
                       $errormsg = "The total grade criteria for each quarter should equal to 100%";
                    
      
        }
       
        
        return $errormsg;
    }
    
    
    

    
    
    public function addToDB($Subject){
        
        //insert subject to DB
        $insertSubjectQuery=mysql_query("INSERT INTO subject (subjectName,passingGrade)
                  VALUES ('".$Subject->getSubjectName()."',".$Subject->getPassingGrade().")");
       
       //get id generated by insert query 
        $this->idSubjectGenerated= mysql_insert_id();
      
       //insert levels of subject to DB
        for($counter=0;$counter<sizeof($Subject->getSubjectLevel());$counter++){
            
           if($Subject->getIndividualSubjectLevel($counter)==""){
                break;
           }
            
            $insertSubjectYearLevelQuery=mysql_query("INSERT INTO subject_yearlevel (Level,subject_idSubject) VALUES ('".$Subject->getIndividualSubjectLevel($counter)."',".$this->idSubjectGenerated.")");
        
            
        }
     
      
       //add relationship of teachers handling this subject and students having this subject
       $this->relationshipHandler=new RelationshipHandler();
        
       $relateTeacherToSubjectQuery=$this->relationshipHandler->TeacherToSubject($Subject->getTeacherInCharge(),$this->idSubjectGenerated);
        
        
       $relateStudentsToSubjectQuery=$this->relationshipHandler->StudentsToSubject($this->idSubjectGenerated);
           
       
       //check if queries are successful
       if($insertSubjectQuery&&$insertSubjectYearLevelQuery&&$relateTeacherToSubjectQuery&&$relateStudentsToSubjectQuery){
      
         
         
       }
        
       else{
            die('Could not connect:' . mysql_error());
        }
        
    }
   
   
    public function addCriterionToDB($gradeCriteria){
        
        //insert gradecriteria of a subject to DB
         for($i=0;$i<sizeof($gradeCriteria);$i++){
                                                
            if($gradeCriteria[$i]==""){
                    break;
             }
            $insertGradeCriterionQuery=mysql_query("INSERT INTO gradecriterion(name,percentage,quarter,subject_idSubject)
             VALUES ('".$gradeCriteria[$i]->getName()."',".$gradeCriteria[$i]->getPercentage().",'".$gradeCriteria[$i]->getQuarter()."',".$this->idSubjectGenerated.")" );
            }   
           
        
           
             
    }
    
    public function searchDB($string) {
        
        //search a subject or subjects
        $searchSubjectQuery= mysql_query("SELECT * FROM subject WHERE subjectName LIKE '%$string%'");
        
        
        return $searchSubjectQuery;
    }
    
        
     public function getCountInDB($string){
         //get number of subjects returned by search DB
            $countResultsQuery=mysql_query("SELECT count(*) AS 'num' FROM (SELECT * FROM subject WHERE subjectName LIKE '%$string%') t");
       
            return $countResultsQuery;
            
     }
          
    
      public function updateDB($Subject) {
                   //update subject
                   $updateSubjectQuery=mysql_query("UPDATE subject SET subjectName = '".$Subject->getSubjectName()."' , 
                                      passingGrade = ".$Subject->getPassingGrade()." WHERE idSubject=".$Subject->getIdSubject());
         
                   //delete levels of subject
                   $deleteExistingLevelsQuery= mysql_query("DELETE FROM subject_yearlevel WHERE subject_idSubject='".$Subject->getIdSubject()."'");
                     

                      
                    
                  //replace levels deleted
                     for($counter=0;$counter<sizeof($Subject->getSubjectLevel());$counter++){
            
                                     if($Subject->getIndividualSubjectLevel($counter)==""){
                                    break;
                        }
            
                        $insertSubjectYearLevelQuery=mysql_query("INSERT INTO subject_yearlevel (Level,subject_idSubject) VALUES ('".$Subject->getIndividualSubjectLevel($counter)."',".$Subject->getIdSubject().")");
        
            
                     }
     
            
            
            
                 //update relationship of subject to teacher
                 $updateTeacherInCharge= mysql_query("UPDATE teacher_has_subject SET idTeacher=".$Subject->getTeacherInCharge()." WHERE idSubject=".$Subject->getIdSubject() );


                 //update relationship of students to subject
                 $this->relationshipHandler=new RelationshipHandler();
                 
                 $relateStudentsToSubjectQuery=$this->relationshipHandler->updateStudentstoSubject($Subject->getIdSubject());

                 
                 //check if queries are successful
                 if($updateSubjectQuery&&$deleteExistingLevelsQuery&&$insertSubjectYearLevelQuery&&$updateTeacherInCharge&&$relateStudentsToSubjectQuery){
                       
                 }
                    else{
                        die('Could not connect: ' . mysql_error());

                    }

      }
      
      
      
    
    public function updateCriterionDB($gradeCriteria){
       
        //update grade criteria in DB
        for($i=0;$i<sizeof($gradeCriteria);$i++){
                  if($gradeCriteria[$i]==""){
                 break;
                 }
                                                                  
          $updateGradeCriterionQuery= mysql_query("UPDATE gradecriterion SET subject_idSubject=".$this->idSubjectGenerated.", name ='".$gradeCriteria[$i]->getName()."' ,percentage =".$gradeCriteria[$i]->getPercentage()."
                  WHERE idGradeCriteria='".$gradeCriteria[$i]->getIdGradeCriterion() ."'")or die('123Could not connect: ' . mysql_error());
                                                                       
                                    
       }      
            
            
            //check if query is successful
            if(!$updateGradeCriterionQuery){
                   return die('Could not connect: ' . mysql_error());
            } 
        
        
             return $updateGradeCriterionQuery;
     }
    
            
   
    
    
    
    public function viewDB() {
        
        //select all subjects in DB
         $subjects=mysql_query("SELECT * FROM Subject") or die('Could not connect: ' . mysql_error());;
         
         return $subjects;
    }
      
      
      public function delete($idSubject) {
          
            //delete levels of subject
           $deleteSubjectLevelQuery=mysql_query("DELETE FROM subject_yearlevel WHERE subject_idSubject='".$idSubject."'"); 
                           
           //delete grades based on grade criteria of subject
           $deleteGradesQuery=mysql_query("DELETE FROM grade WHERE gradecriterion_subject_idSubject='".$idSubject."'") ; 
                          
           //delete grade criteria of subject
           $deleteGradeCriteriaQuery=mysql_query("DELETE FROM gradecriterion WHERE subject_idSubject='".$idSubject."'") ;
                           
          //delete relationship of teacher and subject
           $deleteTeachetoSubjectQuery=mysql_query("DELETE FROM teacher_has_subject WHERE idSubject='".$idSubject."'") ;
                            
          //delete relationship of students to subject
           $deleteSubjecToStudentQuery=mysql_query("DELETE FROM student_has_subject WHERE idSubject='".$idSubject."'")  ;
                           
          //delete subject
           $deleteSubject=mysql_query("DELETE FROM subject WHERE idSubject='".$idSubject."'") ;
           
           
           //check if queries are successful               
           if($deleteSubject&&$deleteSubjectLevelQuery&&$deleteGradesQuery&&$deleteGradeCriteriaQuery&&$deleteTeachetoSubjectQuery 
                   &&$deleteSubjecToStudentQuery){
              
           }
           
           else{
               die('Connection error'.mysql_error());
           }
           
           
      }
      
   public function viewEditSubject($idSubject) {
      //view subject     
         $subjects=mysql_query("SELECT * FROM subject WHERE idSubject='".$idSubject."'") or 
                            die('1Could not connect: ' . mysql_error());
         
         $getsubjectlvl =  mysql_query("SELECT Level FROM subject_yearlevel  WHERE subject_idSubject='".$idSubject."' " )or die('3Could not connect: ' . mysql_error());
         
         $subjectsRow = mysql_fetch_array($subjects);
    
         extract($subjectsRow);
         
         echo "<form action='editSubjectDB.php' method='post'>
              <table>
              <tr><td><p>Name of Subject:</p></td><td><input  class='tf_effect' style='padding:0;' value='".$subjectName."'type='text' name='subjName' />
                  <tr><td><p>Passing Grade of Subject </p></td><td><input  class='tf_effect' onkeypress='return isNumberKey(event)' maxlength='2' value='".$passingGrade."'type='text' name='passing' /></td></tr>
                                            <tr><td><p>For Year Level: </p></td><td> ";
        
        $level=array();
        $i=0;
        while($row = mysql_fetch_array($getsubjectlvl)){
            
            extract($row);
      
            
            $level[$i]=$Level;
            $i++;
        }
        
        if(in_array('Grade 1',$level)==TRUE){
           echo "<p><input type='checkbox' type='text' name='level[]' value='Grade 1' checked/> Grade 1";
        }
        
        else{
             echo "<p><input type='checkbox' type='text' name='level[]' value='Grade 1'/> Grade 1";
        }
        
        if(in_array('Grade 2',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='Grade 2' checked/> Grade 2";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='Grade 2'/> Grade 2";
        }
        
         if(in_array('Grade 3',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='Grade 3' checked/> Grade 3";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='Grade 3'/> Grade 3";
        }
        
        if(in_array('Grade 4',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='Grade 4' checked/> Grade 4";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='Grade 4'/> Grade 4";
        }
        
        
         if(in_array('Grade 5',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='Grade 5' checked/> Grade 5";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='Grade 5'/> Grade 5";
        }
        
        
         if(in_array('Grade 6',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='Grade 6' checked/> Grade 6";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='Grade 6'/>Grade 6<br/>";
        }
        
        if(in_array('1st Year',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='1st Year' checked/> 1st Year";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='1st Year'/> 1st Year";
        }
        
        
         
        if(in_array('2nd Year',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='2nd Year' checked/> 2nd Year";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='2nd Year'/>2nd Year";
        }
        
        
         
        if(in_array('3rd Year',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='3rd Year' checked/> 3rd Year";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='3rd Year' /> 3rd Year";
        }
        
        
         
        if(in_array('4th Year',$level)==TRUE){
           echo "<input type='checkbox' type='text' name='level[]' value='4th Year' checked/>4th Year</td></tr>  </p>";
        }
        
        else{
             echo "<input type='checkbox' type='text' name='level[]' value='4th Year'/>4th Year</td></tr>  </p>";
        }
        
        
         
   
    
     $teacherID= mysql_query("SELECT idTeacher FROM teacher_has_subject WHERE idSubject='".$idSubject."'")or die('1Could not connect: ' . mysql_error());
     $teacherIDRow = mysql_fetch_array($teacherID); 
    
     extract($teacherIDRow);

     $teacherincharge = mysql_query("SELECT idTeacher,teacherName FROM Teacher WHERE idTeacher='$idTeacher'")or die('2Could not connect: ' . mysql_error());
     $teachers=mysql_query("SELECT idTeacher,teacherName FROM teacher;") or  die('4Could not connect: ' . mysql_error()); 
     
     
        echo"  <tr><td><p>Teacher in Charge</p></td><td><select name='teacher'>";
       
      
      while($row = mysql_fetch_array($teacherincharge)){
                                                      
             extract($row);
                                                  
             echo" <option value='$idTeacher'>$teacherName</option><br/>";
             
             $teacherIncharge=$teacherName;
                                                  
                                                  
      }
                                                       
  
     while($row = mysql_fetch_array($teachers)){
                                                      
                  extract($row);
                  
                  if($teacherIncharge==$teacherName){
                      
                  }
                  else echo" <option value='$idTeacher'>$teacherName</option><br/>";
                                                  
      }
                  echo "</select> </td></tr></table>";
                                                   
                  echo "<p>Grade Criteria:</p>";                         
             
                  $this->viewEditGradeCriteria($idSubject,"1st Quarter","criteriaID[]","criteria[]","critPercent[]","newcriteria[]","newcritPercent[]");
                  $this->viewEditGradeCriteria($idSubject,"2nd Quarter","criteria1ID[]","criteria1[]","critPercent1[]","newcriteria1[]","newcritPercent1[]");
                  $this->viewEditGradeCriteria($idSubject,"3rd Quarter","criteria2ID[]","criteria2[]","critPercent2[]","newcriteria2[]","newcritPercent2[]");
                  $this->viewEditGradeCriteria($idSubject,"4th Quarter","criteria3ID[]","criteria3[]","critPercent3[]","newcriteria3[]","newcritPercent3[]");
                   
                   
                   
      echo "<br/><br/><input type='hidden' name='subjectID' value='$idSubject'/>";
      echo "<center><input type='submit' id='addbutton' class='addmore' value='Save' name='editSubject'/></center><br/>";
        
    }
      
            
   
    
    private function viewEditGradeCriteria($idSubject,$quarter,$idCrit,$criteria,$percent,$newCriteria,$newPercent){
        //view grade criteria of subject
        
        $criterion = mysql_query("SELECT * FROM gradecriterion  WHERE subject_idSubject='".$idSubject."' and quarter = '".$quarter."'" )or die('3Could not connect: ' . mysql_error());
    
          echo "
                <table id=".$quarter."><p>".$quarter."</p>";
         
          
          while($criterionRow = mysql_fetch_array( $criterion )){
    
                extract($criterionRow);
          
           echo " <tr><td><input type='hidden' name='".$idCrit."' value='$idGradeCriteria'/></td></tr>";                                        
           echo " <tr><td><p>Name:</p></td><td> <input value='".$name."' class='tf_effect' style='padding:0;' type='text' name='".$criteria."' /> </td><td><p>Percentage: </p></td><td><input type='text' value='".$percentage."' onkeypress='return isNumberKey(event)' maxlength='3' class='tf_effect' style='padding:0;' name='".$percent."' /></td>";
          
           
          }
               
         echo "<input type=hidden name=submitted value=1>";
        
        
         switch($quarter){
             
             case "1st Quarter": 
                
                 echo "<input type='button' onmousedown='addInput1stQuarter();' class='addmore' value='Add More Criteria'/></td></tr>" ;
                  echo "<table id='1stQuarter'></table>";
                 break;
             
             case "2nd Quarter": 
               
                 echo "<input type='button' onmousedown='addInput2ndQuarter();'  class='addmore' value='Add More Criteria'/>" ;
                 echo "<table id='2ndQuarter'></table>";
                 break;
             
              case "3rd Quarter": 
            
                 echo "<input type='button' onmousedown='addInput3rdQuarter();'  class='addmore' value='Add More Criteria'/>" ;
                 echo "<table id='3rdQuarter'></table>";
                 break;
             
              case "4th Quarter": 
               
                 echo "<input type='button' onmousedown='addInput4thQuarter();' class='addmore' value='Add More Criteria'/>" ;
                 echo "<table id='4thQuarter'></table>";
                 break;
             
         }
         
        

    }
         
     
    
    
    

    
  
  
}

?>
